package com.qen.common;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;

import com.qen.constant.DataBaseConstants;
import com.qen.enums.SqlKeyword;
import org.beetl.sql.clazz.ClassDesc;
import org.beetl.sql.clazz.kit.BeanKit;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.mapping.type.JavaSqlTypeHandler;
import org.beetl.sql.core.page.PageRequest;
import org.beetl.sql.core.page.PageResult;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author licz
 * @data 2025年05月15日 9:41 AM
 */
public class BeetlUtils {

    /**
     * 获取SQLManager对象
     *
     * @return
     */

    public static SQLManager getSQLManager() {
        return SpringUtil.getBean("sqlManager", SQLManager.class);
    }

    public static <T> PageResult<T> executePageable(String sql, Map<String, Object> param, PageRequest<T> pageRequest, Class<?> clazz) {
        SQLManager sqlManager = getSQLManager();
        Long count = null;
        List list;
        if (pageRequest.isTotalRequired()) {
            String countSql = sqlManager.getSqlManagerExtend().getPageKit().getCountSql(sqlManager.getDbStyle().getName(), sql);
            count = queryResultSize(sqlManager, parseSqlTemplate(countSql, param), param);
            if (count == null || count == 0L) {
                list = new ArrayList<>();
                return pageRequest.of((List<T>) list, 0L);
            }
        }
        long pageSize = pageRequest.getPageSize();
        Object offset = pageRequest.getStart(sqlManager.isOffsetStartZero());
        String pageSql = sqlManager.getDbStyle().getRangeSql().toRange(sql, offset, pageSize);

        if (clazz != null && !clazz.isAssignableFrom(Map.class)) {
            // 如果 T 是实体类
            list = sqlManager.execute(parseSqlTemplate(pageSql, param), clazz, param);
        } else {
//            List<Map> sqlResult = sqlManager.execute(parseSqlTemplate(pageSql, param), Map.class, param);
//            list = convertResult(sqlResult, lowerCase);
            list = sqlManager.execute(parseSqlTemplate(pageSql, param), Map.class, param);
        }
        return pageRequest.isTotalRequired() ? pageRequest.of((List<T>) list, count) : pageRequest.of((List<T>) list);
    }

    /**
     * 兼容Group By 查询，返回记录总数；记录总数（-2,147,483,648 到 2,147,483,647）超出会有问题，一般使用group by的记录条数不会超过2,147,483,647；
     * TODO 可以进行扩展DBstyle，查询记录总数的sql， 修改为子查询；存在理论List.size() 溢出的可能，暂时未处理；
     *
     * @param sql    统计的sql
     * @param params 查询参数
     * @return 记录总数
     */
    private static Long queryResultSize(SQLManager sqlManager, String sql, Map<String, Object> params) {
        List<Long> list = sqlManager.execute(parseSqlTemplate(sql, params), Long.class, params);
        if (list.isEmpty()) {
            return 0L;
        } else if (list.size() > 1) {
            //仅在group by 时存在多条记录；此时使用总记录数
            return (long) list.size();
        } else {
            return list.get(0);
        }
    }

    /**
     * 转换sql  :parameterName 转换  #{parameterName}
     *
     * @param sqlTemplate 执行的sql，如果是beetlsql模板则不转换，如果是sql模板则转换
     * @return 转换后的sql
     */
    public static String parseSqlTemplate(String sqlTemplate, Object paramSource) {
        if (sqlTemplate.contains(":")) {
            // 处理 IN 语句的特殊情况，将 IN 语句中的参数名转换为 Beetl 的 join 函数
            Pattern inPattern = Pattern.compile("in\\s*:\\b(\\w+)\\b", Pattern.CASE_INSENSITIVE);
            Matcher inMatcher = inPattern.matcher(sqlTemplate);
            StringBuffer inSb = new StringBuffer();
            while (inMatcher.find()) {
                String paramName = inMatcher.group(1);
                // 获取参数值（从 Map 或 Bean 中）
                Object paramValue = getParamValue(paramName, paramSource);
                if (paramValue == null || (paramValue instanceof Collection && ((Collection<?>) paramValue).isEmpty())) {
                    setParamValue(paramName, paramSource);
                }
                inMatcher.appendReplacement(inSb, "in (#{join(" + paramName + ")})");
            }
            inMatcher.appendTail(inSb);
            sqlTemplate = inSb.toString();
            // 正则表达式匹配 :parameterName 并替换为 #{parameterName}，跳过时间格式、JSON 和字符串字面量
            Pattern pattern = Pattern.compile(":(\\w+)(?!\\d|\\-\\d|:\\d)(?=(?:(?:[^']*'[^']*')*[^']*)$)(?=(?:(?:[^{}]*\\{[^{}]*\\})*[^{}]*))");
            Matcher matcher = pattern.matcher(sqlTemplate);
            StringBuffer sb = new StringBuffer();
            while (matcher.find()) {
                matcher.appendReplacement(sb, "#{" + matcher.group(1) + "}");
            }
            matcher.appendTail(sb);
            return sb.toString();
        }
        return sqlTemplate;
    }

    private static Object getParamValue(String paramName, Object paramSource) {
        if (paramSource instanceof Map) {
            // 如果来源是 Map，则通过 Map 获取参数值
            Map<String, Object> paramMap = (Map<String, Object>) paramSource;
            return paramMap.get(paramName);
        } else if (paramSource != null) {
            // 如果是 Bean，则通过反射获取参数值
            try {
                Method method = paramSource.getClass().getMethod("get" + capitalize(paramName)); // 获取 getter 方法
                return method.invoke(paramSource); // 调用 getter 方法获取值
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        return null;
    }

    // 统一获取并修改参数值的方法
    private static void setParamValue(String paramName, Object paramSource) {
        if (paramSource instanceof Map) {
            // 如果来源是 Map，则直接修改 Map 中的值
            Map<String, Object> paramMap = (Map<String, Object>) paramSource;
            Object paramValue = paramMap.get(paramName);

            // 如果 IN 参数为空，初始化为 new Object[1]
            if (paramValue == null || (paramValue instanceof Collection && ((Collection<?>) paramValue).isEmpty())) {
                paramMap.put(paramName, new Object[1]);
            }
        } else if (paramSource != null) {
            // 如果是 Bean 类型，通过反射修改参数值
            try {
                Method getterMethod = paramSource.getClass().getMethod("get" + capitalize(paramName)); // 获取 getter 方法
                Method setterMethod = paramSource.getClass().getMethod("set" + capitalize(paramName), getterMethod.getReturnType()); // 获取 setter 方法

                Object paramValue = getterMethod.invoke(paramSource);
                // 如果 IN 参数为空，初始化为 new Object[1]
                if (paramValue == null || (paramValue instanceof Collection && ((Collection<?>) paramValue).isEmpty())) {
                    setterMethod.invoke(paramSource, new Object[1]);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    // 首字母大写，符合 Java bean 的规范
    private static String capitalize(String str) {
        return str.substring(0, 1).toUpperCase() + str.substring(1);
    }

    /**
     * 由于Beetlsql 查询返回的为List<Map> 将其转换为List<Map<String,Object>> 存在类型检查错误提示；
     *
     * @param list 查询结果
     * @return 转换后的List<Map < String, Object>>
     */
    public static List<Map<String, Object>> convertResult(List<Map> list) {
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        for (int i = 0, size = list.size(); i < size; i++) {
            Set<Map.Entry<String, Object>> entrySet = list.get(i).entrySet();
            Map<String, Object> map = new HashMap<String, Object>();
            for (Map.Entry<String, Object> entry : entrySet) {
                map.put(entry.getKey(), entry.getValue());
            }
            result.add(map);
        }
        return result;
    }

    public static <T> T getDao(Class<?> clazz) {
        Class<?> dao = TableDaoHelper.getDao(clazz);
        Assert.notNull(dao, "dao not find by class <%s>", clazz.getSimpleName());
        return (T) dao;
    }

    /**
     * 针对Mysql进行的 InsertOrUpdate操作
     *
     * @param uniqueKeyNames
     * @param tableName
     * @param classDesc
     * @param list
     * @param <E>
     */
    public static <E> void mysqlInsertOrUpdate(String[] uniqueKeyNames, String tableName, ClassDesc classDesc, E... list) {
        SQLManager sqlManager = getSQLManager();
        StringBuilder sql = new StringBuilder()
                .append(SqlKeyword.INSERT.getSqlSegment())
                .append(StrUtil.SPACE)
                .append(tableName)
                .append(StrUtil.SPACE);
        List<String> colList = new ArrayList<>();
        List<List<String>> valList = new ArrayList<>();
        List<String> indexList = new ArrayList<>();
        List<String> uniqueList = Arrays.asList(uniqueKeyNames);

        // 需要过滤掉的列名，主键列名 和 所有取值为null的列
        List<String> excludesColumn = new ArrayList<>();
        for (String inCol : classDesc.getInCols()) {
            excludesColumn.addAll(classDesc.getIdCols());
            boolean flag = true;
            for (E e : list) {
                String propertyName = sqlManager.getNc().getPropertyName(inCol);
                Object pk = BeanKit.getBeanProperty(e, propertyName);
                if (pk != null) {
                    flag = false;
                }
            }
            if (flag) {
                excludesColumn.add(inCol);
            }
        }
        for (String inCol : classDesc.getInCols()) {
            if (excludesColumn.contains(inCol)) {
                continue;
            }
            String propertyName = sqlManager.getNc().getPropertyName(inCol);
            colList.add("`" + inCol + "`");
            if (!uniqueList.contains(propertyName)) {
                indexList.add("`" + inCol + "`" + SqlKeyword.EQ.getSqlSegment() + SqlKeyword.VALUES + DataBaseConstants.LEFT_BRACKET + "`" + inCol + "`" + DataBaseConstants.RIGHT_BRACKET);
            }
        }
        Map<String, Object> param = new HashMap<>();
        for (int n = 0; n < list.length; n++) {
            E e = list[n];
            List<String> cs = new ArrayList<>();
            for (String inCol : classDesc.getInCols()) {
                if (excludesColumn.contains(inCol)) {
                    continue;
                }
                String propertyName = sqlManager.getNc().getPropertyName(inCol);
                Object pk = BeanKit.getBeanProperty(e, propertyName);
                cs.add(DataBaseConstants.HASH_LEFT_BRACE + propertyName + n + StrUtil.DELIM_END);
                param.put(propertyName + n, pk);
            }
            valList.add(cs);
        }

        sql.append(StrUtil.SPACE)
                .append(DataBaseConstants.LEFT_BRACKET)
                .append(StrUtil.join(StrUtil.COMMA, colList))
                .append(DataBaseConstants.RIGHT_BRACKET)
                .append(StrUtil.SPACE)
                .append(SqlKeyword.VALUES.getSqlSegment());
        for (int i = 0; i < valList.size(); i++) {
            if (i > 0) {
                sql.append(StrUtil.COMMA);
            }
            sql.append(DataBaseConstants.LEFT_BRACKET)
                    .append(StrUtil.join(StrUtil.COMMA, valList.get(i)))
                    .append(DataBaseConstants.RIGHT_BRACKET);
        }
        sql.append(StrUtil.SPACE)
                .append(SqlKeyword.DUPLICATE.getSqlSegment())
                .append(StrUtil.SPACE)
                .append(StrUtil.join(StrUtil.COMMA, indexList));
        int i = sqlManager.executeUpdate(sql.toString(), param);
    }

    /**
     * 根据类型获取对应的JavaSqlTypeHandler
     *
     * @param type
     * @return
     */
    public static JavaSqlTypeHandler getJavaSqlTypeHandler(Class<?> type) {
        return BeetlUtils.getSQLManager().getDefaultBeanProcessors().getHandler(type);
    }

    /**
     * 取注解字段的具体指
     * @param obj
     * @param clz
     * @return
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     */
    public static Object getAnnotatedFieldValue(Object obj, Class... clz) throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
        Class<?> clazz = obj.getClass();
        for (Field field : clazz.getDeclaredFields()) {
            for (int i = 0; i < clz.length; i++) {
                if (field.isAnnotationPresent(clz[i])) {
                    String methodName = "get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
                    Method method = clazz.getMethod(methodName);
                    return method.invoke(obj);
                }
            }
        }
        throw new IllegalArgumentException("No @AssignID or @AutoId annotated field found");
    }

}
